﻿using System;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Collections.Generic;

//自定义命名空间
using DreamCube.Foundation.Basic.Utility;

namespace DreamCube.Framework.DataAccess.Basic
{
    /// <summary>
    /// 数据库的查询的扩展方法，专门提供给BasicTable、GenericBasicTable使用的
    /// </summary>
    public abstract partial class Database
    {
        /// <summary>
        /// 根据条件获取数据，分页接口(不对传入的strWhere和orderBy参数进行验证，效率或许会快一点点)
        /// </summary>
        /// <param name="tableName">要操作的数据库的表名</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="primaryKey">主键列名</param>
        /// <param name="totalRecord">总记录数</param>
        /// <param name="column">表示要查询的列</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应sql语句中where；如果不传入此参数，则默认查询表的所有数据</param>
        /// <param name="orderBy">(语句不可以包含order by关键字)对应配置文件中的数据库名字；如果不传入此参数，则默认查询第一个数据库</param>
        /// <returns></returns>
        internal protected abstract DataTable Get(String tableName,
                                                  Int32 pageIndex,
                                                  Int32 pageSize,
                                                  String primaryKey,
                                                  out Int32 totalRecord,
                                                  String column = "*",
                                                  String strWhere = "",
                                                  String orderBy = "");

        /// <summary>
        /// 更新记录
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="tableName">对应的数据库表名</param>
        /// <param name="command">命令对象（通过命令对象，可以控制在一个事务中执行更新操作）</param>
        /// <returns></returns>
        public Boolean UpdateModel<TModel>(TModel model, String tableName, DbCommand command = null)
            where TModel : ModelObject
        {
            String updateStr = String.Empty;
            this.BuildUpdateOperationAllParam(model, tableName, ref command);
            Int32 result = this.ExecuteNonQuery(command);
            return result > 0;
        }

        /// <summary>
        /// 增加记录
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="model"></param>
        /// <param name="tableName">对应的数据库表名</param>
        /// <param name="command">命令对象，通过换入一个命令对象，可以控制操作在事务中执行</param>
        /// <returns></returns>
        public Boolean AddModel<TModel>(TModel model, String tableName, DbCommand command = null)
            where TModel : ModelObject
        {
            PropertyInfo identityColumnPropertyInfo = null;
            this.BuildAddOperationAllParam(model, tableName, ref command, out identityColumnPropertyInfo);
            Int32 result = -1;  //影响行数
            if (identityColumnPropertyInfo != null)
            {
                command.CommandText += ";select @identityReturnValX = @@identity;";
                this.AddOutParameter(command, "@identityReturnValX", DbType.Int32);
                result = this.ExecuteNonQuery(command);
                //设置model对象值
                identityColumnPropertyInfo.SetValue(model, command.Parameters["@identityReturnValX"].Value, null);
            }
            else result = this.ExecuteNonQuery(command);
            return result > 0;
        }

        /// <summary>
        /// 判断数据库是否存在某记录
        /// </summary>
        /// <param name="tableName">要操作的数据库的表名</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应sql语句中where；</param>
        /// <returns>返回布尔型，true表示存在符合条件的记录，false则相反</returns>
        public Boolean Exist(String tableName, String strWhere)
        {
            String sql = String.Format("select 1 from {0} {1}", tableName, " where " + strWhere);
            using (IDataReader reader = this.ExecuteReader(sql))
            {
                return reader.Read();
            }
        }

        /// <summary>
        /// 删除数据表记录的方法(提供了基础实现)
        /// </summary>
        /// <param name="tableName">要操作的数据库的表名</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应sql语句中where；如果不传入此参数，则默认查询表的所有数据</param>
        /// <returns>返回影响的行数</returns>
        public Int32 Delete(String tableName, String strWhere)
        {
            String sql = String.Format("delete from {0} {1}", tableName, String.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere);
            return this.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 获取指定某列的值（如果存在多条记录的话，只返回第一条的该列的值）
        /// </summary>
        /// <param name="tableName">要操作的数据库的表名</param>
        /// <param name="columnName">需要查询的列名</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应的where语句</param>
        /// <returns></returns>
        public Object GetColumnValue(String tableName, String columnName, String strWhere = "")
        {
            String sql = String.Format("select {0} from {1} {2}",
                                       columnName,
                                       tableName,
                                       String.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere);
            return this.ExecuteScalar(sql);
        }

        /// <summary>
        /// 获取数据记录
        /// </summary>
        /// <param name="tableName">要操作的数据库的表名</param>
        /// <param name="column">需要查询的列名，可以传入*</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应sql语句中where；如果不传入此参数，则默认查询表的所有数据</param>
        /// <param name="orderBy">(语句不可以包含order by关键字)对应sql语句中order by；如果不传入此参数，则不会执行排序操作</param>
        /// <returns></returns>
        public DataTable Get(String tableName, String column, String strWhere, String orderBy)
        {
            if (String.IsNullOrEmpty(column)) column = "*";
            String sql = String.Format("select {0} from {1} {2} {3}",
                                       column,
                                       tableName,
                                       String.IsNullOrEmpty(strWhere) ? "" : " where " + strWhere,
                                       String.IsNullOrEmpty(orderBy) ? "" : " order by " + orderBy);
            return this.ExecuteTable(sql);
        }

        /// <summary>
        /// 从数据库获取一行数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="column"></param>
        /// <param name="strWhere"></param>
        /// <param name="orderBy"></param>
        /// <returns></returns>
        public DataRow GetDataRow(String tableName, String column, String strWhere, String orderBy)
        {
            DataTable table = Get(tableName, column, strWhere, orderBy);
            if (table.Rows.Count > 0)
                return table.Rows[0];
            else
                return null;
        }

        /// <summary>
        /// 根据表名，以及筛选条件，获取一个实体对象
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="tableName">对应的数据表名</param>
        /// <param name="column">需要返回的列数据</param>
        /// <param name="strWhere">筛选条件</param>
        /// <returns></returns>
        public TModel GetModel<TModel>(String tableName, String column = "*", String strWhere = "")
            where TModel : ModelObject, new()
        {
            TModel model = new TModel();
            DataTable table = this.Get(tableName, column, strWhere, "");
            if (table == null || table.Rows.Count == 0) return null;
            MyObject.LoadFieldValueFromDataRow(table.Rows[0], model);
            return model;
        }

        /// <summary>从数据库中获取值。并且返回包含值的对象模型List，一行数据对应一个TModel对象，TModel对象的字段对应行的同名列数据</summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="tableName">对应的数据表名</param>
        /// <param name="column">需要查询的列名，可以传入*</param>
        /// <param name="strWhere">(语句不可以包含where关键字)对应sql语句中where；如果不传入此参数，则默认获取所有的数据</param>
        /// <param name="orderBy">(语句不可以包含order by关键字)对应sql语句中order by；如果不传入此参数，则不会执行排序操作</param>
        /// <returns></returns>
        public List<TModel> GetModelList<TModel>(String tableName, String column = "*", String strWhere = "", String orderBy = "")
            where TModel : ModelObject, new()
        {
            List<TModel> modelList = new List<TModel>();
            TModel model = new TModel();
            DataTable table = this.Get(tableName, column, strWhere, orderBy);
            if (table == null || table.Rows.Count == 0) return null;
            for (Int32 i = 0, j = table.Rows.Count; i < j; i++)
            {
                MyObject.LoadFieldValueFromDataRow(table.Rows[i], model);
                modelList.Add(model);
                //新建model对象实例
                model = new TModel();
            }
            return modelList;
        }

        /// <summary>
        /// 创建update语句的相关参数
        /// </summary>
        /// <param name="model"></param>
        /// <param name="tableName">对应的数据表名</param>
        /// <param name="command"></param>
        internal protected void BuildUpdateOperationAllParam<TModel>(TModel model, String tableName, ref DbCommand command)
            where TModel : ModelObject
        {
            //必须用model实例去获取属性，不能用model type去获取属性，否则会获取到反射类型的属性
            Dictionary<PropertyInfo, DBColumnAttribute> dbcolumns = MyType.GetAttributesOfProperties<DBColumnAttribute>(model, false);
            StringBuilder updateStrBuilder = new StringBuilder();
            List<String> whereStr = new List<String>();
            Int32 i = 0;
            if (command == null) command = this.CreateCommand();
            foreach (KeyValuePair<PropertyInfo, DBColumnAttribute> column in dbcolumns)
            {
                String columnName = column.Value.ColumnName;
                String paramName = this.FormatParameterName(columnName);
                //收集自增列（然后跳过配置自增列的参数)
                if (column.Value.IsIdentity) continue;
                Object propertyVal = MyReflection.GetPropertyValue<Object>(column.Key.Name, model, DBNull.Value, null, null);
                if (column.Value.ParseToDB != null) propertyVal = column.Value.ParseToDB(propertyVal);
                //如果是主键，则跳过此参数
                if (column.Value.IsPrimaryKey)
                {
                    whereStr.Add(String.Format("{0}={1}", columnName, paramName));
                    this.AddInParameter(command, paramName, column.Value.DbType, propertyVal);
                    continue;
                }
                //添加参数
                this.AddInParameter(command, paramName, column.Value.DbType, propertyVal);
                //构建SQL语句
                if (i == 0)
                {
                    updateStrBuilder.AppendFormat("{0}={1}", columnName, this.FormatParameterName(columnName));
                    i++;
                }
                else updateStrBuilder.AppendFormat(",{0}={1}", columnName, this.FormatParameterName(columnName));
            }
            String sql = "update {0} set {1} {2}";
            if (whereStr.Count == 0)
                throw new Exception("提供的数据模型没有指定主键列，无法更新到数据库中");
            command.CommandText = String.Format(sql, tableName, updateStrBuilder.ToString(), String.Format("where {0}", MyEnumerable.JoinEx(whereStr, ",")));
        }

        /// <summary>
        /// 创建insert语句的相关的参数(框架内部调用的方法，不建议用户自己调用)
        /// </summary>
        /// <param name="model"></param>
        /// <param name="tableName">对应的数据表名</param>
        /// <param name="command">命令对象</param>
        /// <param name="identityProperty"></param>
        internal protected void BuildAddOperationAllParam<TModel>(TModel model, String tableName, ref DbCommand command, out PropertyInfo identityProperty)
            where TModel : ModelObject
        {
            //必须用model实例去获取属性，不能用model type去获取属性，否则会获取到反射类型的属性
            Dictionary<PropertyInfo, DBColumnAttribute> dbcolumns = MyType.GetAttributesOfProperties<DBColumnAttribute>(model, false);
            StringBuilder columnNameBuilder = new StringBuilder();
            StringBuilder paramNameBuilder = new StringBuilder();
            Int32 i = 0;
            if (command == null) command = this.CreateCommand();
            PropertyInfo identityColumnPropertyInfo = null;
            foreach (KeyValuePair<PropertyInfo, DBColumnAttribute> column in dbcolumns)
            {
                String columnName = column.Value.ColumnName;
                String paramName = this.FormatParameterName(columnName);
                //收集自增列（然后跳过配置自增列的参数)
                if (column.Value.IsIdentity)
                {
                    identityColumnPropertyInfo = column.Key;
                    continue;
                }
                Object propertyVal = MyReflection.GetPropertyValue<Object>(column.Key.Name, model, DBNull.Value, null, null);
                if (column.Value.ParseToDB != null) propertyVal = column.Value.ParseToDB(propertyVal);
                //添加参数
                this.AddInParameter(command, paramName, column.Value.DbType, propertyVal);
                //构建SQL语句
                if (i == 0)
                {
                    columnNameBuilder.Append(columnName);
                    paramNameBuilder.Append(this.FormatParameterName(columnName));
                    i++;
                }
                else
                {
                    columnNameBuilder.Append("," + columnName);
                    paramNameBuilder.Append("," + paramName);
                }
            }
            String sql = "insert into {0}({1}) values({2})";
            command.CommandText = String.Format(sql, tableName, columnNameBuilder.ToString(), paramNameBuilder.ToString());
            identityProperty = identityColumnPropertyInfo;
        }
    }
}
